package Utils::DbUtils;

use strict;
use warnings;
use 5.010;

use DBI;
use Exporter qw(import);
use Mojo::Log;

our @EXPORT_OK = qw(open_db close_db query_for_array update update_by_map count);
our $DB_FILE = "data/data.db";
our $DB_URL = "dbi:SQLite:dbname=$DB_FILE";

my $log = Mojo::Log->new;

sub open_db {
	my $dbh = DBI->connect($DB_URL, undef, undef, {sqlite_unicode => 1})
		or die $DBI::errstr;
	
	return $dbh;
}

sub close_db {
	my ($dbh) = @_;

	$dbh->disconnect();
}

sub query_for_array {
	my $query_sql = shift;
	my $element_class = shift;
	my @query_params = @_;
	
	my $dbh = open_db();
	my $sth = $dbh->prepare($query_sql);
	my @res = ();

	$sth->execute(@query_params);
	$log->debug("Executing SQL: $query_sql\nParams: @query_params");

	while (my $rows = $sth->fetchrow_hashref) {
		my $item;
		eval "\$item = $element_class"."->new;";
		
		foreach (keys %$rows) {
			$log->debug("\$item->_set_$_\("."\$rows->\{$_\}"."\);");
			eval "\$item->_set_$_\("."\$rows->\{$_\}"."\);";
		}

		push @res, $item;
	}

	$sth->finish();
	close_db($dbh);

	return @res;
}

sub count {
    my $count_sql = shift;
    my @select_params = @_;

    my $dbh = open_db();
    my $sth = $dbh->prepare($count_sql);
    my @res = ();
    my $count = undef;

    $sth->execute(@select_params);
    $log->debug("Doing count operation: $count_sql\nParams: @select_params");
    
    while (my $row = $sth->fetchrow_arrayref) {
        $count = $row->[0];
        unless (defined $count) {
            $count = 0;
        }
        last;
    }

    $sth->finish();
    close_db($dbh);

    return $count;
}

sub update {
	my $update_sql = shift;
	my @update_params = @_;
	my $dbh = open_db();

	my $rows_affected = $dbh->do($update_sql, undef, @update_params);

	$log->debug("Executing SQL: $update_sql\nParams: @update_params");

	close_db($dbh);

	return $rows_affected;
}

sub update_by_map {
    my ($table_name, $id, $map) = @_;
    my $sql = "UPDATE $table_name SET %FIELDS_AND_VALUES_PAIR% WHERE id = $id";
    my $index = 0;
    my $fields_and_values = '';
	my $dbh = open_db();
    
    while (my ($field, $value) = each %$map) {
        if ($index++ > 0) {
            $fields_and_values .= ' AND ';
        }
        
        $fields_and_values .= "$field = \'$value\'";
    }
    
    $sql =~ s/%FIELDS_AND_VALUES_PAIR%/$fields_and_values/;
    $log->debug("Update_By_Map SQL: $sql");

	my $rows_affected = $dbh->do($sql, undef);
    
    close_db($dbh);
    
    return $rows_affected;
}

1;
